const express = require('express');
const router = express.Router();
const {getDb} = require('../dao/db'); // 导入数据库连接函数


const pageSizeInt = 10; // 每页显示的记录数

router.get('/books', (req, res) => {
  let page = req.query.page;
  if (!page) {
    page = "1";
  }
  let pageInt = parseInt(page);
  
  if (isNaN(pageInt)) {
    pageInt = 1; // 如果无法转换为数字，则默认为第一页;
  }
  // 查询总页数
  let db = getDb();
  db.get("SELECT COUNT(*) AS total FROM books", (err, row) => {
    if (err) {
      console.error("查询总页数出错:", err);
      res.status(500).json({ status:500,msg: '查询总页数出错' });
      return;
    }
    let total = row.total;
    let totalPages = Math.ceil(total / pageSizeInt); // 计算总页数	
    if (pageInt > totalPages) {
      pageInt = totalPages; // 如果请求的页码超出总页数，则返回最后一页的数据;	
    }
    //查询当前页的数据
    let offset = (pageInt - 1) * pageSizeInt; // 计算偏移量
    db.all("SELECT book_id, isbn, title, author, publisher, publish_date, stock FROM books LIMIT ? OFFSET ?", [pageSizeInt, offset], (err, rows) => {
      if (err) {
        console.error("查询当前页数据出错:", err);
        res.status(500).json({ status:500,msg: '查询当前页数据出错' });
        return;	
      }	
      res.json({ status:0,msg: 'OK',data: rows,
        current_page: pageInt,page_size: pageSizeInt,total_items: total,total_pages: totalPages });
    })
  })
  
});

router.post('/jwt/bookAdd', (req, res) => {
    let isbn = req.body.isbn || '';
    let title = req.body.title || '';
    let author = req.body.author || '';
    let publisher = req.body.publisher || '';
    let publishDate = req.body.publish_date || '';
    let stock = req.body.stock || 0;
    let createdBy = req.myuser.userId || 0; // 从上下文中获取创建人ID;;

    if (!isbn || !title ) {
      return res.status(400).json({ status:400,msg: 'isbn, title 不能为空' });
    }
    let db = getDb();
    // 检查图书是否已存在
    db.get("SELECT * FROM books WHERE isbn =?", isbn, (err, row) => {
      if (err) {
        console.error("查询图书信息出错:", err);
        res.status(500).json({ status:500,msg: '查询图书信息出错' });
        return;	
      }	
      if (row) {
        return res.status(400).json({ status:400,msg: '图书已存在' });	
      }
      // 插入新图书
      db.run("INSERT INTO books (isbn, title, author, publisher, publish_date, stock, created_by) VALUES (?, ?, ?, ?, ?, ?, ?)", [isbn, title, author, publisher, publishDate, stock, createdBy], (err) => {
        if (err) {
          console.error("插入图书信息出错:", err);	
          res.status(500).json({ status:500,msg: '插入图书信息出错' });
        }	
        res.json({ status:0,msg: 'OK' });
      })
    })
    
  });

  router.put('/jwt/bookUpdate', (req, res) => {
    let book_id = req.body.book_id;
    let isbn = req.body.isbn || '';
    let title = req.body.title || '';
    let author = req.body.author || '';
    let publisher = req.body.publisher || '';
    let publishDate = req.body.publish_date || '';
    let stock = req.body.stock || 0;
    let createdBy = req.myuser.userId || 0; // 从上下文中获取创建人ID;
    const date = new Date();
    const year = date.getFullYear().toString().padStart(4, '0');
    const month = (date.getMonth() + 1).toString().padStart(2, '0');
    const day = date.getDate().toString().padStart(2, '0');
    const hour = date.getHours().toString().padStart(2, '0');
    const minute = date.getMinutes().toString().padStart(2, '0');
    const second = date.getSeconds().toString().padStart(2, '0');
    //console.log(`${year}-${month}-${day} ${hour}:${minute}:${second}`);
    let updated_at = `${year}-${month}-${day} ${hour}:${minute}:${second}`; // 获取当前时间;

    if (!book_id  ) {
      return res.status(400).json({ status:400,msg: 'book_id 不能为空' });
    }
    let db = getDb();
    // 更新图书
    db.run("UPDATE books SET isbn=?, title=?, author=?, publisher=?, publish_date=?, stock=?, created_by=?,updated_at=? WHERE book_id=?", [isbn, title, author, publisher, publishDate, stock, createdBy,updated_at, book_id], (err) => {
      if (err) {
        console.error("更新图书信息出错:", err);
        res.status(500).json({ status:500,msg: '更新图书信息出错' });
        return;	
      }	
      res.json({ status:0,msg: 'OK' });
    })
    
  });

  router.delete('/jwt/bookDel', (req, res) => {
    let book_id = req.query.book_id;
    
    if (!book_id  ) {
      return res.status(400).json({ status:400,msg: 'book_id 不能为空' });
    }
    let db = getDb();
    // 删除图书
    db.run("DELETE FROM books WHERE book_id=?", book_id, (err) => {
      if (err) {
        console.error("删除图书信息出错:", err);
        res.status(500).json({ status:500,msg: '删除图书信息出错' });
        return;
      }
      res.json({ status:0,msg: 'OK' });	
    })
    
  });


  module.exports = router;


